﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for FlightDAL
/// </summary>
public class FlightDAL
{
    DABasic _dabasic;
    public FlightDAL()
    {
        _dabasic = new DABasic();
    }


    public DataSet SearchFirstFlight(int routeid,DateTime departdate,int NumberOfPassenger)
    {
        string _command = "_Flight_search";

        SqlParameter[] _para = new SqlParameter[3];
        _para[0] = new SqlParameter("@routeid", SqlDbType.Int);
        _para[0].Value = routeid;

        _para[1] = new SqlParameter("@departdate", SqlDbType.DateTime);
        _para[1].Value = departdate;

        _para[2] = new SqlParameter("@passenger", SqlDbType.Int);
        _para[2].Value = NumberOfPassenger;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    public DataSet SearchNextFlights(int routeid, DateTime departTime, int NumberOfPassenger)
    {
        string _command = "_Flight_searchTransit";

        SqlParameter[] _para = new SqlParameter[3];
        _para[0] = new SqlParameter("@routeid", SqlDbType.Int);
        _para[0].Value = routeid;

        _para[1] = new SqlParameter("@departTime", SqlDbType.DateTime);
        _para[1].Value = departTime;

        _para[2] = new SqlParameter("@passenger", SqlDbType.Int);
        _para[2].Value = NumberOfPassenger;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    //public DataSet GetFlightInfo()
    //{
    //}

    //public DataSet GetFlightSchedule()
    //{
    //}

    internal DataSet GetNearestLocation(double longitude, double latitude, string country)
    {
        string _command = "_Cities_FindNearestPlace";

        SqlParameter[] _para = new SqlParameter[3];
        _para[0] = new SqlParameter("@long", SqlDbType.Float);
        _para[0].Value = longitude;
        _para[1] = new SqlParameter("@lat", SqlDbType.Float);
        _para[1].Value = latitude;
        _para[2] = new SqlParameter("@country", SqlDbType.NVarChar,50);
        _para[2].Value = country;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    internal List<object> GetRoute(Location origin, Location destination, string DayofWeek)
    {
        string _command = "_Route_FindRoute";

        SqlParameter[] _para = new SqlParameter[4];
        _para[0] = new SqlParameter("@originID", SqlDbType.Int);
        _para[0].Value = origin.ID;
        _para[1] = new SqlParameter("@destinationID", SqlDbType.Int);
        _para[1].Value = destination.ID;
        _para[2] = new SqlParameter("@Date", SqlDbType.VarChar,50);
        _para[2].Value = DayofWeek;
        _para[3] = new SqlParameter("@status", SqlDbType.VarChar, 20);
        _para[3].Direction = ParameterDirection.Output;

        return _dabasic.ExecuteReturnBoth(_command, _para, CommandType.StoredProcedure);
    }

    internal DataSet GetLocationByID(int ID)
    {
        string _command = "_Cities_GetByID";

        SqlParameter[] _para = new SqlParameter[1];
        _para[0] = new SqlParameter("@CityID", SqlDbType.Int);
        _para[0].Value = ID;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    internal DataSet GetLocationByName(string name, string country)
    {
        string _command = "_Cities_GetPlace";

        SqlParameter[] _para = new SqlParameter[2];
        _para[0] = new SqlParameter("@place", SqlDbType.NVarChar,50);
        _para[0].Value = name;
        _para[1] = new SqlParameter("@country", SqlDbType.NVarChar,50);
        _para[1].Value = country;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    internal DataSet getFlightbyID(int FlightID)
    {
        string _command = "_Flight_GetbyID";

        SqlParameter[] _para = new SqlParameter[1];
        _para[0] = new SqlParameter("@flightid", SqlDbType.Int);
        _para[0].Value = FlightID;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    internal DataSet SearchSimilarFlight(int originid, int destinationid, int numofPass, int numofMonth)
    {
        string _command = "_Flight_FindSimilar";

        SqlParameter[] _para = new SqlParameter[4];
        _para[0] = new SqlParameter("@originid", SqlDbType.Int);
        _para[0].Value = originid;
        _para[1] = new SqlParameter("@destinationid", SqlDbType.Int);
        _para[1].Value = destinationid;
        _para[2] = new SqlParameter("@numofPass", SqlDbType.Int);
        _para[2].Value = numofPass;
        _para[3] = new SqlParameter("@numofMonth", SqlDbType.Int);
        _para[3].Value = numofMonth;

        return _dabasic.ExecuteGetDataSet(_command, _para, CommandType.StoredProcedure);
    }

    internal void CancelFlight(int flightID)
    {
        string _command = "UPDATE Airline_Flight SET FlightStatus = 'Cancel' WHERE (FlightID = @flightID)";

        SqlParameter[] _para = new SqlParameter[1];
        _para[0] = new SqlParameter("@flightID", SqlDbType.Int);
        _para[0].Value = flightID;

        _dabasic.ExecuteGetDataSet(_command, _para, CommandType.Text);

    }
}
